package com.jason.www.util;

import com.jason.www.exception.CoreException;
import com.jason.www.util.ExcelParserStrategy.CellComparator;
import com.jason.www.util.ExcelParserStrategy.CellConfig;
import com.jason.www.util.ExcelParserStrategy.MergedRegion;
import com.jason.www.util.ExcelParserStrategy.MergedRegionComparator;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

public class PoiHelper {
	private static final Logger logger = Logger.getLogger(PoiHelper.class);

	public static List<?> readTable(InputStream ips, ExcelParserStrategy strategy, String fileName)
			throws IOException, CoreException {
		Workbook wb = null;
		if ("xls".equals(getFileSuffix(fileName))) {
			wb = new HSSFWorkbook(ips);
		} else if ("xlsx".equals(getFileSuffix(fileName))) {
			wb = new XSSFWorkbook(ips);
		}
		String sheetName = strategy.getSheetName();
		Sheet sheet = null;
		if (null == sheetName) {
			sheet = wb.getSheetAt(0);
		} else {
			sheet = wb.getSheet(sheetName);
		}
		if (sheet == null) {
			throw new CoreException("excel为空，不能解析");
		}
		Row row = sheet.getRow(strategy.getHeaderStartRow());
		// 验证并整理单元格配置
		setCellsIndex(strategy, row);
		// 验证并整理表格中的合并单元个
		setMergedRegion(strategy, sheet);
		List<Object> objs = new LinkedList<Object>();
		try {
			int i = strategy.getHeaderStartRow() + 1;
			row = sheet.getRow(i);
			Iterator<MergedRegion> itr = strategy.getMergedRegions().iterator();
			MergedRegion region = null;
			if(itr.hasNext()){
				region = itr.next();
			}
			while (row != null) {
				Object obj = strategy.getClazz().newInstance();
				int rowInterval = 0;
				if (region != null && region.getRowFrom() == i) {
					rowInterval = region.getRowTo() - region.getRowFrom();
					if(itr.hasNext()){
						region = itr.next();
					}
				}
				List<Row> childRows = new ArrayList<Row>(rowInterval+1);
				childRows.add(row);
				while (rowInterval > 0) {
					childRows.add(sheet.getRow(++i));
					rowInterval--;
				}
				setObjVals(strategy, childRows, obj);
				objs.add(obj);
				row = sheet.getRow(++i);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new CoreException("策略配置错误，参数类型不正确或关联类无实例化方法", e);
		}
		return objs;
	}

	private static String getFileSuffix(String fileName) {
		int index = fileName.lastIndexOf('.');
		if (index == -1 || index == (fileName.length() - 1)) {
			return null;
		}
		return fileName.substring(index + 1);
	}

	/**
	 * 设置cell的坐标，并排序
	 *
	 * @param strategy
	 * @param header
	 * @throws CoreException
	 */
	private static void setCellsIndex(ExcelParserStrategy strategy, Row header) throws CoreException {
		String[] headNames = strategy.getHeaderNames();
		CellConfig[] cells = strategy.getCells();
		int start = strategy.getHeaderStartCol();
		int end = strategy.getHeaderEndCol();
		if (headNames.length != cells.length) {
			throw new CoreException("表头长度设置不正确，请确定解析策略是否有误");
		}
		for (int i = start; i <= end; i++) {
			Cell cell = header.getCell(i);
			String cellVal = cell.getStringCellValue();
			short indx = getHeaderIndx(headNames, cellVal);
			if (indx == -1) {
				throw new CoreException("表头名称不正确，请确定解析策略是否有误:" + cells[i].getValName());
			}
			cells[indx].setIndex(i);
		}
		if (!strategy.isAllCellIndexed()) {
			throw new CoreException("解析策略与表结构不一致，请检查解析策略");
		}
		Arrays.sort(strategy.getCells(), new CellComparator());
	}

	private static short getHeaderIndx(String[] headNames, String headName) {
		if (headName == null) {
			return -1;
		}
		for (short i = 0; i < headNames.length; i++) {
			if (headName.equals(headNames[i])) {
				return i;
			}
		}
		return -1;
	}

	private static void setMergedRegion(ExcelParserStrategy strategy, Sheet sheet) throws CoreException {
		List<MergedRegion> regions = new LinkedList<MergedRegion>();
		int regionSum = sheet.getNumMergedRegions();
		for (int i = 0; i < regionSum; i++) {
			MergedRegion region = new MergedRegion();
			CellRangeAddress addr = sheet.getMergedRegion(i);
			region.setRowFrom(addr.getFirstRow());
			region.setRowTo(addr.getLastRow());
			region.setColFrom(addr.getFirstColumn());
			region.setColTo(addr.getLastColumn());
			regions.add(region);
		}
		strategy.setMergedRegions(regions);
		Collections.sort(strategy.getMergedRegions(), new MergedRegionComparator());
		int bodyStart = strategy.getHeaderStartRow() + 1;
		int j = 0;
		while (j < regions.size()) {
			if (regions.get(j).getRowFrom() != bodyStart) {
				regions.remove(j);
			} else {
				j = regions.size();
			}
		}
		strategy.validateMergeRegionAndSimple();
	}

	private static void setObjVals(ExcelParserStrategy strategy, List<Row> rows, Object obj)
			throws Exception {
		CellConfig[] cells = strategy.getCells();
		for (CellConfig cellConfig : cells) {
			if (!cellConfig.isArr()) {
				setNormalVal(rows, obj, cellConfig);
			} else {
				setArrlVal(rows, obj, cellConfig);
			}
		}
	}

	/**
	 * 数组类型值设置
	 *
	 * @param rows
	 * @param obj
	 * @param cellConfig
	 * @throws Exception
	 * @throws CoreException
	 */
	private static void setArrlVal(List<Row> rows, Object obj, CellConfig cellConfig) throws Exception, CoreException {
		List<String> vals = new ArrayList<String>(rows.size());
		for (int i = 0; i < rows.size(); i++) {
			Cell cell = rows.get(i).getCell(cellConfig.getIndex());
			String val = "";
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					val = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					Boolean val1 = cell.getBooleanCellValue();
					val = val1.toString();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					Double val3 = cell.getNumericCellValue();
					val = val3.toString();
					break;
				case Cell.CELL_TYPE_BLANK:
					break;
				default:
					throw new CoreException("数据类型配置不正确");
			}
			vals.add(val);
		}
		setObjField(obj, cellConfig.getValName(), String[].class, vals.toArray(new String[0]));
	}
	/**
	 * 普通类型值设置
	 *
	 * @param rows
	 * @param obj
	 * @param cellConfig
	 * @throws Exception
	 * @throws CoreException
	 */
	private static void setNormalVal(List<Row> rows, Object obj, CellConfig cellConfig)
			throws Exception, CoreException {
		Cell cell = rows.get(0).getCell(cellConfig.getIndex());
		String val = "";
		if(cell != null){
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					val = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					Boolean val1 = cell.getBooleanCellValue();
					val = val1.toString();
					break;
				case Cell.CELL_TYPE_NUMERIC:
//				Double val3 = cell.getNumericCellValue();
//				val = val3.toString();
					if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
						Date theDate = cell.getDateCellValue();
						SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						val = dff.format(theDate);
					}else{
						DecimalFormat df = new DecimalFormat("0");
						val = df.format(cell.getNumericCellValue());
					}
					break;
				case Cell.CELL_TYPE_BLANK:
					break;
				default:
					throw new CoreException("数据类型配置不正确");
			}
		}
		setObjField(obj, cellConfig.getValName(), String.class, val);
	}

	private static <T> void setObjField(Object pojo, String fieldName, Class<T> valType, T val) throws Exception {
		if(StringUtils.isEmpty(fieldName)){
			throw new Exception("errorField");
		}
		StringBuilder setName = new StringBuilder("set");
		setName.append(fieldName.substring(0, 1).toUpperCase());
		setName.append(fieldName.substring(1));
		Method setMethod = pojo.getClass().getMethod(setName.toString(), valType);
		setMethod.invoke(pojo, val);
	}

    public <T> void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String[] columns) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		// 生成表头样式
		HSSFCellStyle headStyle = getHeadCellStyle(workbook);
		// 生成内容样式
		HSSFCellStyle dataStyle = getListCellStyle(workbook);
		// 产生表格标题行
		addHeader(headers, sheet, headStyle);

		// 遍历集合数据，产生数据行
		try {
			addDataInfo(dataset, columns, workbook, sheet, dataStyle);
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		}
	}

	
	public <T> void exportExcel(OutputStream out,List<ExcelSheet<T>> sheetList) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		for (ExcelSheet<T> sheetData: sheetList){
			
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet(sheetData.getTitle());
			// 设置表格默认列宽度为15个字节
			sheet.setDefaultColumnWidth(15);
			// 生成表头样式
			HSSFCellStyle headStyle = getHeadCellStyle(workbook);
			// 生成内容样式
			HSSFCellStyle dataStyle = getListCellStyle(workbook);
			// 产生表格标题行
			addHeader(sheetData.getHeaders(), sheet, headStyle);
			try {
				// 遍历集合数据，产生数据行
				addDataInfo(sheetData.getDataset(), sheetData.getColumns(), workbook, sheet, dataStyle);
			} catch (Exception e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		
		try {
			workbook.write(out);
		} catch (IOException e) {
			logger.error(e);
			e.printStackTrace();
		}
	}
	
	public <T> HSSFWorkbook generateExcel(String title, String[] headers, Collection<T> dataset, String[] columns) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		// 生成表头样式
		HSSFCellStyle headStyle = getHeadCellStyle(workbook);
		// 生成内容样式
		HSSFCellStyle dataStyle = getListCellStyle(workbook);
		// 产生表格标题行
		addHeader(headers, sheet, headStyle);

		// 遍历集合数据，产生数据行
		try {
			addDataInfo(dataset, columns, workbook, sheet, dataStyle);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		}
		
		return workbook;
	}
	
	public <T> void generateMultilSheet(HSSFWorkbook workbook,String title, String[] headers, Collection<T> dataset, String[] columns) {
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		// 生成表头样式
		HSSFCellStyle headStyle = getHeadCellStyle(workbook);
		// 生成内容样式
		HSSFCellStyle dataStyle = getListCellStyle(workbook);
		// 产生表格标题行
		addHeader(headers, sheet, headStyle);

		// 遍历集合数据，产生数据行
		try {
			addDataInfo(dataset, columns, workbook, sheet, dataStyle);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
		}
		
	}

	private HSSFCellStyle getHeadCellStyle(HSSFWorkbook workbook) {
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		return style;
	}

	private HSSFCellStyle getListCellStyle(HSSFWorkbook workbook) {
		// 生成并设置另一个样式
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		HSSFFont font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);
		return style2;
	}

	private <T> void addDataInfo(Collection<T> dataset, String[] heads, HSSFWorkbook workbook, HSSFSheet sheet,
								 HSSFCellStyle dataStyle) throws Exception {
		//加上查询数据为空的判断
		if (null == dataset || dataset.size() < 1){
			return;
		}
		int index = 1;
		for (T rowData : dataset) {
			HSSFRow row = sheet.createRow(index++);
			for (int i = 0; i < heads.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(dataStyle);
				Object val = getObjField(rowData, heads[i]);
				// 判断值的类型后进行强制类型转换
				String textValue = null;
				if (val instanceof Date) {
					Date date = (Date) val;
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					textValue = sdf.format(date);
				} else if (val != null) {
					// 其它数据类型都当作字符串简单处理
					textValue = val.toString();
				}
				if (textValue != null) {
					cell.setCellValue(textValue);
				}
			}
		}
	}

	private void addHeader(String[] headers, HSSFSheet sheet, HSSFCellStyle style) {
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(headers[i]);
		}
	}


	public static Object getObjField(Object pojo, String fieldName) throws Exception {
		if (StringUtils.isEmpty(fieldName)) {
			throw new Exception("errorField");
		}
		StringBuilder getName = new StringBuilder("get");
		getName.append(fieldName.substring(0, 1).toUpperCase());
		getName.append(fieldName.substring(1));
		Method getMethod = pojo.getClass().getMethod(getName.toString());
		return getMethod.invoke(pojo);
	}
	/**
	 * 测试读取excel导出sql语句
	 * @param args
	 * @throws Exception
	 */
	/*public static void main(String[] args) throws Exception {
		FileInputStream fin = null;
		try {
			fin = new FileInputStream(args[0]);
			Workbook wb = new HSSFWorkbook(fin);
			Sheet sheet = wb.getSheetAt(0);
			int totalNum = 0;
			for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
			   Row row = sheet.getRow(i);
			 //如果该行为空，则结束本次循环
               if (row == null) {
                   continue;
               }

               Cell prodIdCell = row.getCell(0);
               String prodId = getCellStringValue(prodIdCell);//.getStringCellValue();
               Cell oldCataIdCell =  row.getCell(2);
               String oldCataId = oldCataIdCell != null ? getCellStringValue(oldCataIdCell): null;
               Cell newCataIdCell =  row.getCell(4);
               String newCataId = newCataIdCell != null ? getCellStringValue(newCataIdCell): null;
               if (newCataId != null && StringUtils.isEmpty(newCataId) && !newCataId.equals(oldCataId)) {
            	   String sql = "update `ibalife_prod`.`product` set cata_id = " + newCataId + " where prod_id = " + prodId + ";";
            	   System.out.println(sql);
//            	   String sql = "insert `ibalife_base`.`base_cata_old_rel`(new_cata_id, old_cata_id) values (" + newCataId + ", " + oldCataId + ");";
//            	   System.out.println(sql);
            	   totalNum ++;
               } else {
//            	   System.out.println(" prodId:" + prodId);
               }
			}

			System.out.println(" sum total is " + totalNum);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (fin != null)
				fin.close();
		}
	}*/
}
